﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Globalization;
using System.Data;
using System.Collections.ObjectModel;

namespace University.Workshop.DataAccess
{
    public static class AuctionDataAccess
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=LQSdataBase; Integrated Security=true";

        public static bool UpdateBid(bool answer, float lastBidPrice, long productId, DateTime lastBidDate, long bidderUserId)
        {
            bool exit = true;
            SqlConnection conne = null;
            try
            {
                conne = new SqlConnection(ConnString);
                SqlCommand change = conne.CreateCommand();
                if (answer)
                {
                    change.CommandText = "UPDATE [Auction] SET Status = @status Where ProductId=@productId";
                    change.Parameters.AddWithValue("status", "Finished");
                    change.Parameters.AddWithValue("productId", productId);
                }
                else
                {
                    change.CommandText = "UPDATE [Auction] SET BidPrice=@lastBidPrice, BidDate=@lastBidDate, BidderID=@bidderUserId Where ProductId=@productId";
                    change.Parameters.AddWithValue("lastBidPrice", lastBidPrice);
                    change.Parameters.AddWithValue("lastBidDate", lastBidDate);
                    change.Parameters.AddWithValue("bidderUserID", bidderUserId);
                    change.Parameters.AddWithValue("productId", productId);
                    
                }
                conne.Open();
                change.ExecuteNonQuery();

            }
            catch (Exception)
            {
                exit = false;
                throw new DataAccessException("Error updating a user");
            }
            finally
            {
                if (conne != null)
                {
                    conne.Close();
                }
                SqlConnection.ClearAllPools();
            }
            return exit;
        }

        public static Collection<ProductAuction> AllAuctions()
        {
            Collection<ProductAuction> result = new Collection<ProductAuction>();
            SqlConnection connection = null;
            SqlCommand command = null;
            SqlDataReader reader = null;
            try
            {
                connection = new SqlConnection(ConnString);
                command = connection.CreateCommand();
                command.CommandText = "select * from Product join Auction on product.ID=Auction.ProductID";
                connection.Open();
                reader = command.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                while (reader.Read())
                {
                    ProductAuction productAuction = new ProductAuction();
                    Product product = new Product();
                    Auction auction = new Auction();
                    product.ProductId = Int64.Parse(reader["ID"].ToString(), CultureInfo.CurrentCulture);
                    product.Name = reader["Name"].ToString();
                    product.Category = reader["Category"].ToString();
                    product.Description = reader["Description"].ToString();
                    product.Postdate = DateTime.Parse(reader["PosDate"].ToString(), CultureInfo.CurrentCulture);
                    product.Price = float.Parse(reader["Price"].ToString(), CultureInfo.CurrentCulture);
                    product.Currency = reader["Currency"].ToString();
                    product.Status = reader["Status"].ToString();
                    product.SellerId = Int64.Parse(reader["SellerID"].ToString(), CultureInfo.CurrentCulture);
                    product.Quantity = short.Parse(reader["Quantity"].ToString(), CultureInfo.CurrentCulture);

                    auction.ProductId = Convert.ToInt64(reader["ProductID"].ToString(), CultureInfo.CurrentCulture);
                    auction.BidderUserId = Convert.ToInt64(reader["BidderID"].ToString(), CultureInfo.CurrentCulture);
                    auction.Currency = reader["Currency"].ToString();
                    auction.Date = DateTime.Parse(reader["Date"].ToString(), CultureInfo.CurrentCulture);
                    auction.LastBidDate = DateTime.Parse(reader["BidDate"].ToString(), CultureInfo.CurrentCulture);
                    auction.LastBidPrice = float.Parse(reader["BidPrice"].ToString(), CultureInfo.CurrentCulture);
                    auction.Status = AuctionState.InProcess;
                    productAuction.Auction = auction;
                    productAuction.Product = product;
                    if (DateTime.Now > auction.Date)
                        productAuction.TimeLeft = System.TimeSpan.Zero;
                    else
                        productAuction.TimeLeft = (auction.Date-DateTime.Now);
                    result.Add(productAuction);
                }
                return result;
            }
            catch (Exception er)
            {
                throw new DataAccessException("Error getting products", er);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
            
        }

        public static Auction GetAuctionByItem(Auction auctionedItem)
        {
            if (auctionedItem != null)
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();


                    cmd.CommandText = "SELECT * FROM [AUCTION] WHERE ProductID=@productId ";
                    cmd.Parameters.AddWithValue("productId", auctionedItem.ProductId);
                    try
                    {
                        conn.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (!reader.HasRows)
                        {
                            return null;
                        }
                        Auction auction = new Auction();

                        while (reader.Read())
                        {
                            auction.Id = Int64.Parse(reader["AuctionID"].ToString(), CultureInfo.CurrentCulture);
                            string status = reader["Status"].ToString();
                            if (status == "Canceled")
                                auction.Status = AuctionState.Canceled;
                            if (status == "InProcess")
                                auction.Status = AuctionState.InProcess;
                            if (status == "Finished")
                                auction.Status = AuctionState.Finished;
                            auction.ProductId = Int64.Parse(reader["ProductID"].ToString(), CultureInfo.CurrentCulture);
                            auction.Date = (DateTime)reader["Date"];
                            auction.BidderUserId = Int64.Parse(reader["BidderID"].ToString(), CultureInfo.CurrentCulture);
                            auction.LastBidDate = (DateTime)reader["BidDate"];
                            auction.LastBidPrice = float.Parse(reader["BidPrice"].ToString(), CultureInfo.CurrentCulture);
                        }
                        return auction;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error getting an auction", ex);
                    }
                    finally
                    {
                        SqlConnection.ClearAllPools();
                    }
                }

            }
            else return null;
 
        }
        public static Auction GetAuctionByproductId(long auctionedProductId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [AUCTION] WHERE ProductID=@productId ";
                cmd.Parameters.AddWithValue("productId", auctionedProductId);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                Auction auction = new Auction();

                while (reader.Read())
                {
                    auction.Id = Int64.Parse(reader["AuctionID"].ToString(), CultureInfo.CurrentCulture);
                    string status = reader["Status"].ToString();
                    if (status == "Canceled")
                        auction.Status = AuctionState.Canceled;
                    if (status == "InProcess")
                        auction.Status = AuctionState.InProcess;
                    if (status == "Finished")
                        auction.Status = AuctionState.Finished;
                    auction.ProductId = Int64.Parse(reader["ProductID"].ToString(), CultureInfo.CurrentCulture);
                    auction.Date = (DateTime)reader["Date"];
                    auction.BidderUserId = Int64.Parse(reader["BidderID"].ToString(), CultureInfo.CurrentCulture);
                    auction.LastBidDate = (DateTime)reader["BidDate"];
                    auction.LastBidPrice = float.Parse(reader["BidPrice"].ToString(), CultureInfo.CurrentCulture);
                }
                return auction;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting an auction", ex);
            }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
        }

        public static void CancelAuction(long auctionedProductId)
        {

            SqlConnection conn = new SqlConnection(ConnString);

                try
                {
                    SqlCommand cmd = conn.CreateCommand();
                    SqlCommand cmd2 = conn.CreateCommand();
                    cmd.CommandText = "UPDATE [dbo].[Auction] SET Status = @status WHERE ProductID=@productId ";
                    cmd2.CommandText = "UPDATE [dbo].[Product] SET Status = @status WHERE ID=@productId ";
                    cmd.Parameters.AddWithValue("status", "Canceled");
                    cmd.Parameters.AddWithValue("productId", auctionedProductId);
                    cmd2.Parameters.AddWithValue("status", "OnSale");
                    cmd2.Parameters.AddWithValue("productId", auctionedProductId);
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error getting an auction");
                    }
                    result = cmd2.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error getting a product");
                    }
                }
                catch (SqlException)
                {
                    throw new DataAccessException("Error getting an auction");
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
        }

        public static void RegisterAuction(Auction auction)
        {
            if (auction != null)
            {
                SqlConnection connection = new SqlConnection(ConnString);
                try
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "INSERT INTO [dbo].[Auction](ProductID,Date,BidDate,BidderID,BidPrice,Status,Currency) VALUES(@productID,@date,@bidDate,@bidderID, @bidPrice, @status, @currency)";
                    command.Parameters.AddWithValue("ProductID", auction.ProductId);
                    command.Parameters.AddWithValue("Date", auction.Date);
                    command.Parameters.AddWithValue("BidDate", auction.Date);
                    command.Parameters.AddWithValue("BidderID", auction.BidderUserId);
                    command.Parameters.AddWithValue("BidPrice", auction.LastBidPrice);
                    command.Parameters.AddWithValue("Status", "InProcess");
                    command.Parameters.AddWithValue("Currency", auction.Currency);
                    connection.Open();
                    int resp = command.ExecuteNonQuery();
                    if (resp <= 0)
                    {
                        throw new DataAccessException("Error inserting auction");
                    }
                }
                catch (Exception ex)
                {
                    throw new DataAccessException("Error inserting auction", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
        }
    }
}
